{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Toree Magics<a name=\"top\"></a>\n",
    "Magics are special \"functions\" which enable features or execute some special code. Magics can receive input arguments when they are invoked. There are two types of magics: `cell` magics and `line` magics. Magics invocations are not case sensitive.\n",
    "\n",
    "**Table of Contents**\n",
    "\n",
    "1. [Line Magics](#line-magics)\n",
    "    1. [LsMagic](#lsmagic)\n",
    "    1. [Truncation](#truncation)\n",
    "    1. [ShowTypes](#showtypes)\n",
    "    1. [AddJar](#addjar)\n",
    "    1. [AddDeps](#adddeps)\n",
    "1. [Cell Magics](#cell-magics)\n",
    "    1. [DataFrame](#dataframe)\n",
    "    1. [Html](#html)\n",
    "    1. [JavaScript](#javascript)\n",
    "    1. [SparkSQL](#sparksql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Line Magics<a name=\"line-magics\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "Line magics are run on a single line and can have other code and line magics within the same cell. Line magics use the following syntax: \n",
    "\n",
    "```\n",
    "%magicname [args]\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %LsMagic<a name=\"lsmagic\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "The `LsMagic` is a magic to list all the available magics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:34.806145Z",
     "start_time": "2019-10-03T21:20:16.633Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%LsMagic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %Truncation<a name=\"truncation\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "Toree will, by default, truncate results from statements. This can be managed through the `%Truncation` magic. To see the current state of the truncation setting you can invoke the magic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:39.100921Z",
     "start_time": "2019-10-03T21:20:38.148Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "// invoke the truncation magic to see if truncation is on or off\n",
    "%Truncation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:41.671097Z",
     "start_time": "2019-10-03T21:20:40.148Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "// return a value to see the truncation\n",
    "(1 to 200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:43.725755Z",
     "start_time": "2019-10-03T21:20:42.380Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%Truncation off\n",
    "(1 to 200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:45.239801Z",
     "start_time": "2019-10-03T21:20:44.321Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%Truncation on\n",
    "(1 to 200)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %ShowTypes<a name=\"showtypes\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "The type information for a result is hidden by default. This behavior can be changed by using the `%ShowTypes` magic. You can view the current state of `%ShowTypes` by invoking it with no arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:46.902857Z",
     "start_time": "2019-10-03T21:20:46.221Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%ShowTypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:48.184907Z",
     "start_time": "2019-10-03T21:20:47.697Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "\"Hello types!\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:49.557754Z",
     "start_time": "2019-10-03T21:20:48.928Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%ShowTypes on\n",
    "\"Hello types!\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:50.989070Z",
     "start_time": "2019-10-03T21:20:50.127Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "(1, \"Hello types!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:52.723947Z",
     "start_time": "2019-10-03T21:20:51.775Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%ShowTypes off\n",
    "\"Hello types!\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %AddJar<a name=\"addjar\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "`AddJar` is a magic that allows the addition of jars to Torree's environment. You can see the arguments for `AddJar` by invoking it with no arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:20:54.701964Z",
     "start_time": "2019-10-03T21:20:54.048Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%AddJar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:09.559217Z",
     "start_time": "2019-10-03T21:20:55.610Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%AddJar https://repo1.maven.org/maven2/org/lwjgl/lwjgl/3.0.0b/lwjgl-3.0.0b.jar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:11.037640Z",
     "start_time": "2019-10-03T21:21:10.429Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "org.lwjgl.Version.getVersion()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## %AddDeps<a name=\"adddeps\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "`AddDeps` is a magic to add dependencies from a maven repository. You can see the arguments for `AddDeps` by invoking it with no arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:12.722001Z",
     "start_time": "2019-10-03T21:21:12.083Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%AddDeps"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note, that by default the `AddDeps` magic will only retrieve the specified dependency. If you want the transitive dependencies provide the `--transitive` flag."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:19.925717Z",
     "start_time": "2019-10-03T21:21:13.463Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%AddDeps org.joda joda-money 0.11 --transitive --trace --verbose"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:20.402022Z",
     "start_time": "2019-10-03T21:21:14.889Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "org.joda.money.CurrencyUnit.AUD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cell Magics<a name=\"cell-magics\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "Cell magics are magics which take the whole cell as their argument. They take the following form:\n",
    "\n",
    "```\n",
    "%%magicname\n",
    "line1\n",
    "line2\n",
    "...\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %%DataFrame<a name=\"dataframe\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "The `%%DataFrame` magic is used to convert a Spark SQL DataFrame into various formats. Currently, `json`, `html`, and `csv` are supported. The magic takes an expression, which evauluates to a dataframe, to perform the conversion. So, we first need to create a DataFrame object for reference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:26.505636Z",
     "start_time": "2019-10-03T21:21:16.559Z"
    },
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "case class DFRecord(key: String, value: Int)\n",
    "val sqlc = spark\n",
    "import sqlc.implicits._\n",
    "val df = sc.parallelize(1 to 10).map(x => DFRecord(x.toString, x)).toDF()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default output is `html`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:27.263295Z",
     "start_time": "2019-10-03T21:21:18.906Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%%dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:31.206958Z",
     "start_time": "2019-10-03T21:21:19.836Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%%dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can specify the `--output` argument to change the output type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:32.753947Z",
     "start_time": "2019-10-03T21:21:20.900Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%%dataframe --output=csv\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is also an option to limit the number of records returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:34.125714Z",
     "start_time": "2019-10-03T21:21:22.141Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%%dataframe --limit=3\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %%Html<a name=\"html\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "The `%%HTML` magic allows you to return HTML."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:34.632182Z",
     "start_time": "2019-10-03T21:21:23.450Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%%html\n",
    "<p>\n",
    "Hello, <strong>Magics</strong>!\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %%JavaScript<a name=\"javascript\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "The `%%JavaScript` magic allows to return JavaScript. The JavaScript code will run in the notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:35.115595Z",
     "start_time": "2019-10-03T21:21:24.902Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%%JavaScript\n",
    "alert(\"Hello, Magics!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### %%SparkSQL<a name=\"sparksql\"></a><span style=\"float: right; font-size: 0.5em\"><a href=\"#top\">Top</a></span>\n",
    "The `%%SparkSQL` magic allows for SQL queries to be performed against tables saved in spark."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:37.549749Z",
     "start_time": "2019-10-03T21:21:25.910Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "val sqlc = spark\n",
    "import sqlc.implicits._\n",
    "case class Record(key: String, value: Int)\n",
    "val df = sc.parallelize(1 to 10).map(x => Record(x.toString, x)).toDF()\n",
    "df.registerTempTable(\"MYTABLE\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:39.369156Z",
     "start_time": "2019-10-03T21:21:26.846Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%%SQL\n",
    "SELECT * FROM MYTABLE WHERE value >= 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-03T21:21:40.452631Z",
     "start_time": "2019-10-03T21:21:28.247Z"
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "%%SQL\n",
    "SELECT * FROM MYTABLE WHERE value >= 4"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Apache Toree - Scala",
   "language": "scala",
   "name": "apache_toree_scala"
  },
  "language_info": {
   "codemirror_mode": "text/x-scala",
   "file_extension": ".scala",
   "mimetype": "text/x-scala",
   "name": "scala",
   "pygments_lexer": "scala",
   "version": "2.11.12"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
